package com.sc.web.gwt.server.persistence;

import com.sc.web.gwt.shared.model.AgentAdmin;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

public class AgentAdminPersistence extends AbstractDBConnection {
    final int _valid = 1;
    final int _suspended = 2;

    public List<AgentAdmin> getAllAgentAdmins() {
        List<AgentAdmin> agentAdminList = new ArrayList<AgentAdmin>();
        String query = "SELECT AgentAdminId, AgentAdminAccount, DES_DECRYPT(AgentAdminPassword), AgentAdminName,AgentAdminEmail, AgentAdminPhoneNumber,\n" +
                "AgentAdminPosition,AgentAdminCompany, SiteName, states.StateId, states.State \n" +
                "FROM AgentAdmins INNER JOIN states\n" +
                "ON agentadmins.StateId = states.StateId";
        try {
            Connection connection = getConnection();
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(query);
            while (resultSet.next()) {
                agentAdminList.add(new AgentAdmin(resultSet.getInt(1),
                        resultSet.getString(2), resultSet.getString(3), resultSet.getString(4),
                        resultSet.getString(5), resultSet.getString(6), resultSet.getString(7),
                        resultSet.getString(8), resultSet.getString(9), resultSet.getInt(10), resultSet.getString(11)));
            }

            resultSet.close();
            connection.close();

        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
        }

        return agentAdminList;
    }

    public List<AgentAdmin> searchAgentAdmins(String searchText, int searchIndex) {
        String searchFor;
        switch (searchIndex) {
            case 0:
                searchFor = "AgentAdminId";
                break;
            case 1:
                searchFor = "AgentAdminAccount";
                break;
            case 2:
                searchFor = "AgentAdminName";
                break;
            case 3:
                searchFor = "AgentAdminEmail";
                break;
            case 4:
                searchFor = "AgentAdminPhoneNumber";
                break;
            case 5:
                searchFor = "AgentAdminPosition";
                break;
            case 6:
                searchFor = "AgentAdminCompany";
                break;
            case 7:
                searchFor = "SiteName";
                break;
            case 8:
                searchFor = "states.State";
                break;
            default:
                searchFor = "AgentAdminId";
                break;
        }
        List<AgentAdmin> agentAdminList = new ArrayList<AgentAdmin>();
        String query = "SELECT AgentAdminId, AgentAdminAccount, DES_DECRYPT(AgentAdminPassword), AgentAdminName,AgentAdminEmail, AgentAdminPhoneNumber, \n" +
                "AgentAdminPosition, AgentAdminCompany, SiteName, states.StateId, states.State \n" +
                "FROM AgentAdmins INNER JOIN states ON agentadmins.StateId = states.StateId \n" +
                "WHERE " + searchFor + " REGEXP ?";
        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(query);
            ps.setString(1, searchText);
            ResultSet resultSet = ps.executeQuery();
            while (resultSet.next()) {
                agentAdminList.add(new AgentAdmin(resultSet.getInt(1),
                        resultSet.getString(2), resultSet.getString(3), resultSet.getString(4),
                        resultSet.getString(5), resultSet.getString(6), resultSet.getString(7),
                        resultSet.getString(8), resultSet.getString(9), resultSet.getInt(10), resultSet.getString(11)));
            }

            resultSet.close();
            connection.close();

        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
        }

        return agentAdminList;
    }

    public AgentAdmin getAgentAdmin(int agentAdminId) {
        AgentAdmin agentAdmin = new AgentAdmin();
        String query = "SELECT AgentAdminId,AgentAdminAccount, DES_DECRYPT(AgentAdminPassword), AgentAdminName,AgentAdminEmail, AgentAdminPhoneNumber,\n" +
                "AgentAdminPosition,AgentAdminCompany, SiteName, states.StateId, states.State \n" +
                "FROM AgentAdmins INNER JOIN states\n" +
                "ON agentadmins.StateId = states.StateId\n" +
                "WHERE AgentAdminId = ?";
        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(query);
            ps.setInt(1, agentAdminId);
            ResultSet resultSet = ps.executeQuery();
            if (resultSet.first()) {
                agentAdmin = new AgentAdmin(resultSet.getInt(1),
                        resultSet.getString(2), resultSet.getString(3), resultSet.getString(4),
                        resultSet.getString(5), resultSet.getString(6), resultSet.getString(7),
                        resultSet.getString(8), resultSet.getString(9), resultSet.getInt(10), resultSet.getString(11));
            }

            resultSet.close();
            connection.close();

        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
        }

        return agentAdmin;
    }

    public boolean updateAgentAdmin(AgentAdmin agentAdmin) {
        String query = "UPDATE agentadmins\n" +
                "SET AgentAdminAccount = ?, AgentAdminPassword = DES_ENCRYPT(?), AgentAdminName = ?, AgentAdminEmail = ?," +
                "AgentAdminPhoneNumber = ?, AgentAdminPosition = ?, AgentAdminCompany = ?, StateId = ?, SiteName = ?\n" +
                "WHERE AgentAdminId = ?";
        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(query);
            ps.setString(1, agentAdmin.getAgentAdminAccount());
            ps.setString(2, agentAdmin.getAgentAdminPassword());
            ps.setString(3, agentAdmin.getAgentAdminName());
            ps.setString(4, agentAdmin.getAgentAdminEmail());
            ps.setString(5, agentAdmin.getAgentAdminPhoneNumber());
            ps.setString(6, agentAdmin.getAgentAdminPosition());
            ps.setString(7, agentAdmin.getAgentAdminCompany());
            if (agentAdmin.getState().equals("Valid")) {
                ps.setInt(8, _valid);
            } else if (agentAdmin.getState().equals("Suspended")) {
                ps.setInt(8, _suspended);
            }
            ps.setString(9, agentAdmin.getSiteName());
            ps.setInt(10, agentAdmin.getAgentAdminId());
            ps.execute();
            connection.close();
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
            return false;
        }

        return true;
    }

    public boolean insertAgentAdmin(AgentAdmin agentAdmin) {
        String query = "INSERT INTO agentadmins (AgentAdminAccount, AgentAdminPassword, AgentAdminName, AgentAdminEmail, AgentAdminPhoneNumber, AgentAdminPosition, AgentAdminCompany, StateId, SiteName)\n" +
                "VALUES (?,DES_ENCRYPT(?),?,?,?,?,?,?,?)";
        try {
            Connection connection = getConnection();
            PreparedStatement ps = connection.prepareStatement(query);
            ps.setString(1, agentAdmin.getAgentAdminAccount());
            ps.setString(2, agentAdmin.getAgentAdminPassword());
            ps.setString(3, agentAdmin.getAgentAdminName());
            ps.setString(4, agentAdmin.getAgentAdminEmail());
            ps.setString(5, agentAdmin.getAgentAdminPhoneNumber());
            ps.setString(6, agentAdmin.getAgentAdminPosition());
            ps.setString(7, agentAdmin.getAgentAdminCompany());
            if (agentAdmin.getState().equals("Valid")) {
                ps.setInt(8, _valid);
            } else if (agentAdmin.getState().equals("Suspended")) {
                ps.setInt(8, _suspended);
            }
            ps.setString(9, agentAdmin.getSiteName());

            ps.execute();
            connection.close();
        } catch (Exception e) {
            System.err.println("Mysql Statement Error: " + query);
            e.printStackTrace();
            return false;
        }

        return true;
    }
}
